package com.service.database.handler;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import com.service.database.config.DataBase;
import com.service.database.constants.JdbcConstants;
import com.service.database.constants.SqlConstants;
import com.service.database.model.DataColumn;
import com.service.database.utils.CommonUtils;
import com.service.database.constants.DbConstants;

import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

/**
 * mysql处理器
 * @author xianggj
 * @Date 2021/6/4 22:16
 **/
public class MysqlHandler extends AbstractDatabaseHandler {

    /**
     * 数值类型小数位最大精度和默认长度
     */
    public static final int MAX_INT_LENGTH = 225;
    public static final int DEFAULT_INT_LENGTH = 10;
    public static final int MAX_FLOAT_LENGTH = 127;
    public static final int DEFAULT_FLOAT_LENGTH = 0;

    public MysqlHandler(DataBase type) {
        super(type);
    }

    @Override
    public String urlSpecialDeal(StringBuffer currentUrl) {
        if (StrUtil.isNotBlank(dbName)){
            currentUrl.append(dbName);
        }
        //用户名和密码连续
        if (StrUtil.isNotBlank(userName) && StrUtil.isNotBlank(pwd)){
            currentUrl.append(linkStr);
            currentUrl.append(JdbcConstants.USER);
            currentUrl.append(userName);
            currentUrl.append(JdbcConstants.PWD);
            currentUrl.append(pwd);
            linkStr = JdbcConstants.AND_MARK;
        }
        //mysql8 以后的url都必须添加时区
        if (DataBase.MYSQL.getDriver().equalsIgnoreCase(DbConstants.MYSQL8_DRIVER)){
            currentUrl.append(linkStr);
            currentUrl.append("useUnicode=true&characterEncoding=UTF-8&userSSL=false&serverTimezone=GMT%2B8");
            linkStr = JdbcConstants.AND_MARK;
        }
        return currentUrl.toString();
    }

    @Override
    public String getShowDataBaseSql() {
        return String.format("select schema_name as %s from INFORMATION_SCHEMA.SCHEMATA", returnKeys);
    }

    @Override
    public String getShowTablesSql() {
        return String.format("select table_name as %s ,table_comment as %s, create_time as %s  " +
                " from INFORMATION_SCHEMA.TABLES " +
                " where table_schema = ?",returnKeys);
    }


    @Override
    public String getShowTableDescSql() {
        StringBuffer sql = new StringBuffer();
        sql.append("select ");
        sql.append(String.format(
                "column_name as %s," +
                "data_type as %s," +
                "CASE WHEN is_nullable = 'YES' THEN '是' ELSE '否' END  as %s," +
                "CASE WHEN column_key = 'PRI' THEN '是' ELSE '否' END  as %s," +
                "CASE WHEN column_type like '%%(%%' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(column_type,'(', -1), ')', 1), ',',1) ELSE '' END as %s," +
                "CASE WHEN column_type like '%%(%%' THEN " +
                        "CASE WHEN column_type like '%%,%%'  THEN " +
                        "SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(column_type,'(', -1), ')', 1), ',',-1) " +
                        "ELSE '' END " +
                "ELSE '' END as %s, " +
                "column_comment as %s ", returnKeys));
        sql.append(String.format("from INFORMATION_SCHEMA.COLUMNS " +
                "Where table_schema = '%s' and table_name = ?", dbName));
        return sql.toString();
    }

    @Override
    public String getShowTableDataSql() {
        return String.format("select * from %s.%s ",
                addChar(dbName), addChar(tableName));
    }

    @Override
    public String dropTableSql() {
        return String.format("DROP TABLE IF EXISTS %s.%s ",
                addChar(dbName), addChar(tableName));
    }

    /**
     * @param columns
     * @return
     */
    @Override
    public List<String> createTableSql(List<DataColumn> columns) {
        StringBuffer sql = new StringBuffer();
        sql.append("CREATE TABLE ");
        sql.append(" IF NOT EXISTS ");
        sql.append( String.format(" %s.%s ",
                addChar(dbName), addChar(tableName)));
        sql.append("(");
        String columnKey = null;
        for (int i = 0; i < columns.size() ; i++) {
            DataColumn dataCol = columns.get(i);
            String columnName = dataCol.getColumnName();
            int dataType = dataCol.getDataTypeInt();
            if (StrUtil.isBlank(columnName) || dataType == 0){
                continue;
            }
            if (dataCol.getColumnKey()){
                columnKey = columnName;
            }

            sql.append(addChar(columnName));
            sql.append(" ");
            addTypeStr(sql, dataCol);
            if (dataCol.getIsNull()){
                sql.append(" NULL ");
            } else {
                sql.append(" NOT NULL ");
            }
            if (StrUtil.isNotBlank(dataCol.getColumnComment())){
                sql.append(" COMMENT ");
                sql.append("'"+StrUtil.removeAll(dataCol.getColumnComment(), '\'')+"'");
            }
            if (i != columns.size() -1){
                sql.append(",");
            }

        }
        if (columnKey != null){
            sql.append(", PRIMARY KEY ("+addChar(columnKey)+") USING BTREE");
        }
        sql.append(") ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic");
        List<String> list = new ArrayList<>();
        list.add(sql.toString());
        return list;
    }

    private void addTypeStr(StringBuffer sql, DataColumn dataCol) {
        String typeStr = intToType(dataCol.getDataTypeInt());
        Integer length = dataCol.getLength();
        Integer floatLength = dataCol.getFloatLength();
        switch (typeStr){
            case "varchar":
                sql.append(typeStr);
                sql.append("(");
                if (length==null || length == 0){
                    length = SqlConstants.DEFAULT_VARCHAR_LENGTH;
                }
                sql.append(length);
                sql.append(")");
                sql.append(" CHARACTER SET utf8 COLLATE utf8_general_ci ");
                break;
            case "int":
            case "char":
            case "binary":
            case "bitint":
                sql.append(typeStr);
                sql.append("(");
                if (length==null || length == 0){
                    length = DEFAULT_INT_LENGTH;
                }
                if (length > MAX_INT_LENGTH){
                    length = MAX_INT_LENGTH;
                }
                sql.append(length);
                sql.append(")");
                break;
            case "double":
            case "float":
            case "decimal":
                sql.append(typeStr);
                sql.append("(");
                if (length==null || length == 0){
                    length = DEFAULT_INT_LENGTH;
                }
                sql.append(length);
                sql.append(",");
                if (floatLength!=null ){
                    //精度不能太大 因为从数据库读的精度有可能是其他数据库的
                    if (floatLength > MAX_FLOAT_LENGTH){
                        floatLength = MAX_FLOAT_LENGTH;
                    }
                    sql.append(floatLength);
                } else {
                    sql.append(DEFAULT_FLOAT_LENGTH);
                }
                sql.append(")");
                break;
            default:
                sql.append(typeStr);
        }
    }

    @Override
    public String getShowTableDataNumSql() {
        return String.format("select count(0) as %s from %s.%s", returnKeys[0],
                addChar(dbName), addChar(tableName));
    }

    @Override
    public String distinctColSql() {
        StringBuffer sql = new StringBuffer("select distinct ");
        List<String> sqls = new ArrayList<>();
        for (int i = 0; i < cols.length; i++) {
            if (returnKeys.length > i){
                sqls.add(String.format(" %s as %s", cols[i], returnKeys[i]));
            } else {
                sqls.add(String.format(" %s", cols[i]));
            }
        }
        sql.append(CollUtil.join(sqls, SqlConstants.COMMA));
        sql.append(String.format(" from %s ", addChar(tableName)));
        return sql.toString();
    }

    @Override
    public String addLimitBySql(String sql, Integer num, Integer offset) {
        String outSql = CommonUtils.substringNotBracketsString(sql);
        //如果包含limit
        if (StrUtil.containsIgnoreCase(outSql, SqlConstants.LIMIT)){
            sql = String.format(SqlConstants.SELECT_CONTAIN, sql);
        }
        if (num == null){
            return sql;
        }
        sql += SqlConstants.LIMIT + num;
        if (offset != null){
            sql += SqlConstants.OFFSET + offset;
        }
        return  sql;
    }

    @Override
    protected String intToType(int i) {
        switch (i){
            case Types.INTEGER:
                return "int";
            case Types.BOOLEAN:
            case Types.CHAR:
                return  "char";
            case Types.TIME:
                return "time";
            case Types.DATE:
                return "date";
            case Types.TIMESTAMP:
            case Types.TIMESTAMP_WITH_TIMEZONE:
                return "datetime";
            case Types.BIGINT:
                return "bigint";
            case Types.DECIMAL:
                return "decimal";
            case Types.DOUBLE:
                return "double";
            case Types.FLOAT:
            case Types.REAL:
                return "float";
            case Types.BINARY:
                return "binary";
            default:
                return "varchar";
        }
    }

}
